import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_省外就业的就业省分布(
    project_id: int,
    questionnaire_ids: List[int],
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None
) -> Dict[str, Any]:
    """
    省外就业的就业省分布 - 指标计算函数
    
    ## 指标说明
    统计毕业生在省外就业的省份分布情况，包括各省份的就业人数及其占总省外就业人数的比例。
    用于分析毕业生跨省就业的主要流向区域。
    
    ## Args
        project_id (int): 项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 问卷ID集合，用于确定数据范围
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 5895,
        "questionnaire_ids": [11158, 11159]
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok", 
        "code": 0,
        "result": [
            {
                "job_province": "广东省",
                "count": 120,
                "percentage": 0.35
            },
            {
                "job_province": "浙江省",
                "count": 80,
                "percentage": 0.23
            }
        ]
    }
    ```
    """
    logger.info(f"开始计算指标: 省外就业的就业省分布, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()  

        # 1. 查询项目配置信息
        project_sql = """
        SELECT client_code, item_year 
        FROM client_item 
        WHERE id = %s
        """
        project_info = db.fetchone(project_sql, (project_id,))
        if not project_info:
            raise ValueError(f"未找到项目ID={project_id}的配置信息")

        client_code = project_info['client_code']
        item_year = project_info['item_year']
        
        logger.info(f"项目配置: client_code={client_code}, item_year={item_year}")

        # 2. 计算 shard_tb_key
        shard_tb_key = re.sub(r'^[A-Za-z]*0*', '', client_code)
        logger.info(f"计算得到 shard_tb_key: {shard_tb_key}")

        # 3. 查询省外就业的就业省分布数据
        student_table = f"dim_client_target_baseinfo_student_calc_{item_year}"
        sql = f"""
        SELECT
            job_province,
            COUNT(*) AS count,
            ROUND(COUNT(*) / (SELECT COUNT(*) FROM {student_table} 
                            WHERE shard_tb_key = %s AND job_province != '' AND job_around = '省外就业'), 4) AS percentage
        FROM
            {student_table}
        WHERE
            shard_tb_key = %s
            AND job_province != ''
            AND job_around = '省外就业'
        GROUP BY
            job_province
        ORDER BY count DESC
        """
        
        results = db.fetchall(sql, (shard_tb_key, shard_tb_key))
        if not results:
            raise ValueError("未找到省外就业数据")
            
        # 处理查询结果
        formatted_results = []
        for row in results:
            formatted_results.append({
                "job_province": row["job_province"],
                "count": row["count"],
                "percentage": float(row["percentage"])
            })

        logger.info(f"指标 '省外就业的就业省分布' 计算成功")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": formatted_results
        }

    except Exception as e:
        logger.error(f"计算指标 '省外就业的就业省分布' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 省外就业的就业省分布",
            "code": 500,
            "error": str(e)
        }